//************************************************************************************************************
// CoNatural.Data.SqlServer.dll
// Author: Roger Torres
//************************************************************************************************************
// You can use, modify, and distribute the source code and executable programs based on the source code.
// This source code is provided "as is" and without warranties as to performance or merchantability. 
// The author and/or distributors of this source code may have made statements about this source code. 
// Any such statements do not constitute warranties and shall not be relied on by the user in deciding 
// whether to use this source code. This source code is provided without any express or implied warranties 
// whatsoever. Because of the diversity of conditions and hardware under which this source code may be used, 
// no warranty of fitness for a particular purpose is offered. The user is advised to test the source code 
// thoroughly before relying on it. The user must assume the entire risk of using the source code.
//************************************************************************************************************

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Text;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using CoNatural.Data;

namespace CoNatural.Data.SqlServer {
   public class SqlServerCommandDeployer : CommandDeployerBase {
      public override void DeployCommands(IConnection connection, IEnumerable<ICommand> iCommands) {
         Server server = new Server(new ServerConnection(new SqlConnection(connection.ConnectionString)));
         Database database = server.Databases[server.ConnectionContext.DatabaseName];
			IDbProvider dbProvider = connection.DataBinder.DbProvider;

         foreach (var iCommand in iCommands) {
				string[] nameParts = dbProvider.ScriptName(iCommand).Replace("[", string.Empty).Replace("]", string.Empty).Split('.');
            string schemaName = nameParts.Length > 1 ? nameParts[0] : "dbo";
            string storedProcedureName = nameParts.Length > 1 ? nameParts[1] : nameParts[0];

            // create schemas
            if (!database.Schemas.Contains(schemaName)) {
               Schema schema = new Schema(database, schemaName);
               schema.Create();
            }

            // drop existing stored procedures
            StoredProcedure storedProcedure = database.StoredProcedures[storedProcedureName, schemaName];
            if (storedProcedure != null)
               storedProcedure.Drop();

            // create stored procedures
            storedProcedure = new StoredProcedure(database, storedProcedureName, schemaName);
            storedProcedure.TextMode = false;
            foreach (var p in Parameter.GetParameters(dbProvider, iCommand.GetType())) {
               StoredProcedureParameter spp = new StoredProcedureParameter(storedProcedure, "@" + p.Name);
               if (p.DbType == DbType.String)
                  spp.DataType = new DataType(SqlDataType.NVarChar, p.Size);
               else if (p.DbType == DbType.Decimal)
                  spp.DataType = new DataType(SqlDataType.Decimal, p.Precision, p.Scale);
               else {
                  SqlParameter sp = (SqlParameter)dbProvider.CreateDataParameter(
							p.Name,
							p.DbType,
							p.Direction,
							p.Size,
							p.Precision,
							p.Scale,
							p.IsNullable,
							p.UdtName,
							null
						);
                  string typeName = Enum.GetName(typeof(SqlDbType), sp.SqlDbType);
                  SqlDataType dataType = (SqlDataType)Enum.Parse(typeof(SqlDataType), typeName);
                  spp.DataType = new DataType(dataType);
               }
               spp.IsOutputParameter = p.Direction != ParameterDirection.Input;
               storedProcedure.Parameters.Add(spp);
            }
				storedProcedure.TextBody = iCommand.GetScript() ?? connection.DataBinder.ScriptProvider.GetScript(iCommand);
            storedProcedure.Create();
         }
      }
   }
}


